import sqlite3
from sqlite3 import Error

def connect_db():
    """创建数据库连接"""
    import os
    db_path = os.path.join(os.path.dirname(__file__), "trade_monitor.db")
    
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        # 补全 CREATE TABLE 语句并调整字段顺序
        conn.execute('''CREATE TABLE IF NOT EXISTS products
                     (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     name TEXT NOT NULL UNIQUE,
                     exchange_code TEXT NOT NULL,
                     margin REAL NOT NULL,
                     stoploss REAL NOT NULL,
                     takeprofit REAL NOT NULL,
                     limit_stoploss REAL DEFAULT 3000, #limit_stoploss
                     is_monitored INTEGER DEFAULT 0,
                     add_time TEXT DEFAULT CURRENT_TIMESTAMP)''')
        return conn
    except Error as e:
        print(f"数据库连接错误: {e}")
        return None

def insert_product(conn, product):
    """添加新品种"""
    # 调整字段顺序与 CREATE TABLE 一致
    sql = '''INSERT INTO products(name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, is_monitored)
             VALUES(?,?,?,?,?,?,?)'''
    cur = conn.cursor()
    try:
        cur.execute(sql, product)
        conn.commit()
        return cur.lastrowid
    except Error as e:
        print(f"添加品种错误: {e}")
        return None

def delete_product(conn, product_id):
    """删除品种"""
    sql = 'DELETE FROM products WHERE id=?'
    cur = conn.cursor()
    try:
        cur.execute(sql, (product_id,))
        conn.commit()
        return cur.rowcount
    except Error as e:
        print(f"删除品种错误: {e}")
        return None

def delete_product_by_name(conn, product_name):
    """根据名称删除品种"""
    sql = 'DELETE FROM products WHERE name=?'
    cur = conn.cursor()
    try:
        cur.execute(sql, (product_name,))
        conn.commit()
        return cur.rowcount
    except Error as e:
        print(f"删除品种错误: {e}")
        return None

def update_product(conn, product_id, updates):
    """更新品种信息"""
    try:
        # 调整字段顺序与 CREATE TABLE 一致
        sql = '''UPDATE products SET 
                name = ?,
                exchange_code = ?,
                margin = ?,
                stoploss = ?,
                takeprofit = ?,
                limit_stoploss = ?,
                is_monitored = ?
                WHERE id = ?'''
        params = (
            updates['name'],
            updates['exchange_code'],
            updates['margin'],
            updates['stoploss'],
            updates['takeprofit'],
            updates['limit_stoploss'],
            updates['is_monitored'],
            product_id
        )
        cur = conn.cursor()
        cur.execute(sql, params)
        conn.commit()
        return cur.rowcount
    except Error as e:
        print(f"更新品种错误: {e}")
        return None

def get_all_products(conn):
    """获取所有品种"""
    cur = conn.cursor()
    try:
        cur.execute("SELECT * FROM products")
        return cur.fetchall()
    except Error as e:
        print(f"获取品种列表错误: {e}")
        return None

def get_monitored_products(conn):
    """获取监控中的品种"""
    cur = conn.cursor()
    try:
        cur.execute("SELECT * FROM products WHERE is_monitored = 1")
        return cur.fetchall()
    except Error as e:
        print(f"获取监控品种错误: {e}")
        return None

def get_product_by_id(conn, product_id):
    """根据ID获取品种信息"""
    cur = conn.cursor()
    try:
        cur.execute("SELECT * FROM products WHERE id=?", (product_id,))
        return cur.fetchone()
    except Error as e:
        print(f"获取品种信息错误: {e}")
        return None

# 测试数据部分需要同步修改
if __name__ == '__main__':
    # 新增数据库连接初始化
    conn = connect_db()
    cur = conn.cursor()  # 创建游标对象
    
    new_2509_products = [
        ("豆油2509", "DCE.y2509", 5800.0, 60.0, 10, 3000.0, 1),  # 调整字段顺序
        ("铁矿石2509", "DCE.i2509", 7500.0, 70.0, 100, 3000.0, 0),
        ("甲醇2509", "CZCE.MA2509", 2800.0, 35.0, 10, 3000.0, 1),
        ("沥青2509", "SHFE.bu2509", 4200.0, 25.0, 10, 3000.0, 0),
        ("热轧卷板2509", "SHFE.hc2509", 4800.0, 45.0, 10, 3000.0, 1),
        ("棉花2509", "CZCE.CF2509", 5500.0, 150.0, 5, 3000.0, 0),
        ("菜籽粕2509", "CZCE.RM2509", 3200.0, 28.0, 10, 3000.0, 1),
        ("动力煤2509", "CZCE.ZC2509", 6800.0, 15.0, 100, 3000.0, 0),
        ("沪镍2509", "SHFE.ni2509", 28000.0, 500.0, 1, 3000.0, 1),
        ("白银2509", "SHFE.ag2509", 8500.0, 5.0, 15, 3000.0, 0)
    ]

    sample_products = [
        ("螺纹钢2501", "SHFE.rb2501", 5000.0, 50.0, 10, 3000.0, 0),  # 调整字段顺序
        ("棕榈油2509", "DCE.p2509", 6000.0, 80.0, 10, 3000.0, 1),
        ("沪铜2409", "SHFE.cu2409", 35000.0, 200.0, 5, 3000.0, 0),
        ("沪深300", "CFFEX.IF2409", 120000.0, 30.0, 300, 3000.0, 1),
        ("黄金2410", "SHFE.au2410", 40000.0, 2.0, 1000, 3000.0, 0),
        ("原油2409", "INE.sc2409", 80000.0, 10.0, 100, 3000.0, 1),
        ("豆粕2501", "DCE.m2501", 4500.0, 40.0, 10, 3000.0, 0),
        ("PTA2501", "CZCE.TA2501", 3500.0, 60.0, 5, 3000.0, 1),
        ("玻璃2501", "CZCE.FG2501", 3000.0, 30.0, 20, 3000.0, 0),
        ("白糖2501", "CZCE.SR2501", 4000.0, 40.0, 10, 3000.0, 1)
    ]

    cur.executemany('''INSERT INTO products(name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, is_monitored)
                    VALUES(?,?,?,?,?,?,?)''', new_2509_products)

    cur.executemany('''INSERT INTO products(name, exchange_code, margin, stoploss, takeprofit, limit_stoploss, is_monitored)
                    VALUES(?,?,?,?,?,?,?)''', sample_products)

    conn.commit()
    print(f"成功新增{len(new_2509_products) + len(sample_products)}条记录")
    
    # 获取总数
    cur.execute("SELECT COUNT(*) FROM products")
    print(f"当前总记录数: {cur.fetchone()[0]}")
    
    conn.close()